package models.report;

import java.util.*;

import play.Logger;
import play.db.ebean.*;
import play.db.ebean.Model.Finder;
import play.data.validation.Constraints.*;
import play.data.format.*;
import play.mvc.Http.Context;
import play.mvc.Http.Request;
import service.Paging;

import javax.persistence.*;

import models.monitor.ScheduleETL;

import com.avaje.ebean.Ebean;
import com.avaje.ebean.EbeanServer;
import com.avaje.ebean.ExpressionList;
import com.avaje.ebean.Page;
import com.avaje.ebean.PagingList;
import com.avaje.ebean.Query;
import com.avaje.ebean.RawSql;
import com.avaje.ebean.RawSqlBuilder;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnore;

@Entity
public class UsedCoinFact extends Model{
	public Integer dateKey;
	
	public Integer timeKey;
	
	public String dateValue;
	
	@JsonIgnore
	public Long userKey;
	
	@ManyToOne
	@JoinColumn(name="product_key")
	public Product product;
	
	public Integer quantity;	
	
	public double price;
	
	public String type;
	
	public Integer marketKey;
	
	public Integer deviceKey;
	
	// Extra: Product
	public Integer productId;
	public String productName;
	public Integer totalPrice;
	public Integer totalQuantity;
	public Integer totalUser;
	
	public static Finder<Long,UsedCoinFact> find = new Finder(
		Long.class, UsedCoinFact.class
	);
	
	/*
	 * Lưu ý: dùng rawSql không thể dùng pagingList.getTotalRowCount()
	 */
	public static Paging search(String type, int startTime, int endTime, int market){		
		Request request = Context.current().request();
		
		int pageSize = Integer.parseInt(request.getQueryString("pageSize"));
    	int page = Integer.parseInt(request.getQueryString("page"));
    	
    	EbeanServer desperado = Ebean.getServer("desperado");
    	
    	String sql = " select DATE(tb.used_coin_datetime) as dateValue, sum(tb.quantity) as totalQuantity, sum(tb.price) as totalPrice, count(tb.user_key) as totalUser, p.product_id, p.product_name, p.type "  
    			+ " from used_coin_fact tb"   
    			+ " join product p ON p.product_key = tb.product_key "
    			+ " group by tb.date_key, tb.product_key ";  
      
    	RawSql rawSql = RawSqlBuilder.parse(sql).create();
    	//.columnMapping("order_id",  "order.id")  
            
    	Query<UsedCoinFact> query = desperado.find(UsedCoinFact.class);
	    query.setRawSql(rawSql);
	    
	    // Dùng để join
    	//query.fetch("product");
    	ExpressionList<UsedCoinFact> expression = query.where();
    	
    	if(!type.isEmpty()) expression.eq("p.type", type);
    	
    	if(startTime > 0) expression.ge("tb.date_key", startTime);
    	
    	if(endTime > 0) expression.le("tb.date_key", endTime);
    	
    	if(market > 0) expression.eq("tb.market_key", market);
    	
    	PagingList<UsedCoinFact> pagingList = expression.findPagingList(pageSize).setFetchAhead(false);
    	
    	Page<UsedCoinFact> page0 = pagingList.getPage(page - 1);
        List<UsedCoinFact> list = page0.getList();
        
    	Paging paging = new Paging(expression.findList().size(), list);
    	
        return paging;
	}
}
